import pyodbc
from selenium.webdriver.common.by import By
from selenium.common.exceptions import *
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as ec
from selenium import webdriver
import datetime
import pandas as pd
from decimal import Decimal
import os
import config


def get_certificate_info(project_id):
    """根据项目id、证书编号查询证书信息"""

    driver = f'DRIVER={{SQL Server Native Client 11.0}};SERVER=192.168.1.162;DATABASE=LandTax_hebei;UID=sa;PWD=1qazZAQ!'
    conn = pyodbc.connect(driver)
    cursor = conn.cursor()
    sql1 = """ 
            IF exists(SELECT *
                      FROM sys.objects
                      WHERE type = 'P'
                        AND name = 'getBuildingInfo')
            DROP PROCEDURE dbo.getBuildingInfo;       
    """
    sql2 = """
            CREATE PROCEDURE getBuildingInfo @project_id VARCHAR(25)
            AS
            BEGIN
                DECLARE
                    @number_of_buildings VARCHAR(10),
                    @number_of_unit VARCHAR(10),
                    @number_of_floor VARCHAR(10),
                    @floor_height VARCHAR(10),
                    @number_of_households VARCHAR(10),
                    @house_type VARCHAR(500),
                    @building_structure VARCHAR(50),
                    @decoration_type VARCHAR(50),
                    @building_id VARCHAR(50),
                    @area01 VARCHAR(500),
                    @area02 VARCHAR(500),
                    @area03 VARCHAR(500),
                    @house_type_underground01 VARCHAR(500),
                    @number_of_underground_households01 VARCHAR(10),
                    @underground_area01 VARCHAR(500),
                    @underground_floor_height01 VARCHAR(50),
                    @house_type_underground02 VARCHAR(500),
                    @number_of_underground_households02 VARCHAR(50),
                    @underground_area02 VARCHAR(500),
                    @underground_floor_height02 VARCHAR(50)
                SET @house_type = '';SET @area01 = '';SET @area02 = '';SET @area03 = '';SET 
                @house_type_underground01 = '';
                SET @underground_area01 = '';SET @underground_floor_height01 = '';SET 
                @house_type_underground02 = ''
                SET @underground_area02 = ''SET @underground_floor_height02 = '';
                -- 地上
                -- 楼栋数
                SELECT @number_of_buildings = count(*) FROM Lou t WHERE t.Xmid = @project_id;
                -- 楼单元数
                SELECT @number_of_unit = count(*) FROM Danyuan t WHERE t.Danyuan_xmid = @project_id;

                -- 楼层数
                SELECT @number_of_floor = count(*)
                    FROM Louceng t
                    WHERE exists(SELECT 1 FROM Fangwu f WHERE f.loucengGuid = t.loucengGuid AND f.IsDS = '1')
                      AND t.Louceng_xmid = @project_id;
                -- 层高
                SELECT @floor_height = t.lcHeight FROM Louceng t WHERE t.Louceng_xmid = @project_id;
                -- 每单元层户数
                SELECT @number_of_households = count(*)
                    FROM Fangwu t
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.IsDS = '1'
                    GROUP BY t.loucengGuid;
                -- 房屋类型
                SELECT @house_type = @house_type + ZD.FW_MC + ','
                    FROM Fangwu t
                             INNER JOIN ZDfwlx ZD ON t.FW_DM = ZD.FW_DM
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.IsDS = '1';
                -- 建筑结构
                SELECT @building_structure = z.JzjgMc
                    FROM Lou t
                             INNER JOIN Zdjzjglx z ON z.JzjgDm = t.JglxDm
                    WHERE t.Xmid = @project_id;
                -- 装修类型
                SELECT @decoration_type = z.ZxMc
                    FROM Lou t
                             INNER JOIN Zdzxlx z ON z.ZxDm = t.ZxlxDm
                    WHERE t.Xmid = @project_id;
                -- 楼号
                SELECT @building_id = t.louName FROM lou t WHERE t.Xmid = @project_id;
                -- 面积01
                SELECT @area01 = @area01 + CONVERT(VARCHAR(50), t.mianji) + ','
                    FROM Fangwu t
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.fangwuName LIKE '%01'
                      AND t.IsDS = '1';
                -- 面积02
                SELECT @area02 = @area02 + CONVERT(VARCHAR(50), t.mianji) + ','
                    FROM Fangwu t
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.fangwuName LIKE '%02'
                      AND t.IsDS = '1';
                -- 面积03
                SELECT @area03 = @area03 + CONVERT(VARCHAR(50), t.mianji) + ','
                    FROM Fangwu t
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.fangwuName LIKE '%03'
                      AND t.IsDS = '1';

                -- 地下
                -- 地下1
                -- 房屋类型01
                SELECT @house_type_underground01 = @house_type_underground01 + ZD.FW_MC + ','
                    FROM Fangwu t
                             INNER JOIN ZDfwlx ZD ON t.FW_DM = ZD.FW_DM
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.IsDS = '-1'
                      AND t.fangwuName LIKE '-10%';
                -- 层户数01
                SELECT @number_of_underground_households01 = count(*)
                    FROM Fangwu t
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.IsDS = '-1'
                      AND t.fangwuName LIKE '-10%'
                    GROUP BY t.loucengGuid;
                -- 每户面积01
                SELECT @underground_area01 = @underground_area01 + CONVERT(VARCHAR(50), t.mianji) + ','
                    FROM Fangwu t
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.IsDS = '-1'
                      AND t.fangwuName LIKE '-10%';
                -- 层高01
                SELECT @underground_floor_height01 = @underground_floor_height01 + CONVERT(VARCHAR(50), 
                t.lcHeight) + ','
                    FROM Louceng t
                    WHERE t.Louceng_xmid = @project_id
                      AND t.loucengName = '-1';
                -- 地下2
                -- 房屋类型02
                SELECT @house_type_underground02 = @house_type_underground02 + ZD.FW_MC + ','
                    FROM Fangwu t
                             INNER JOIN ZDfwlx ZD ON t.FW_DM = ZD.FW_DM
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.IsDS = '-1'
                      AND t.fangwuName LIKE '-20%';
                -- 层户数02
                SELECT @number_of_underground_households02 = count(*)
                    FROM Fangwu t
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.IsDS = '-1'
                      AND t.fangwuName LIKE '-20%'
                    GROUP BY t.loucengGuid;
                -- 每户面积02
                SELECT @underground_area02 = @underground_area02 + CONVERT(VARCHAR(50), t.mianji) + ','
                    FROM Fangwu t
                    WHERE t.Fangwu_xmid = @project_id
                      AND t.IsDS = '-1'
                      AND t.fangwuName LIKE '-20%';
                -- 层高02
                SELECT @underground_floor_height02 = @underground_floor_height02 + CONVERT(VARCHAR(50), 
                t.lcHeight) + ','
                    FROM Louceng t
                    WHERE t.Louceng_xmid = @project_id
                      AND t.loucengName = '-2';

                SELECT @number_of_buildings                number_of_buildings,
                       @number_of_unit                     number_of_unit,
                       @number_of_floor                    number_of_floor,
                       @floor_height                       floor_height,
                       @number_of_households               number_of_households,
                       @house_type                         house_type,
                       @building_structure                 building_structure,
                       @decoration_type                    decoration_type,
                       @building_id                        building_id,
                       @area01                             area01,
                       @area02                             area02,
                       @area03                             area03,
                       @house_type_underground01           house_type_underground01,
                       @number_of_underground_households01 number_of_underground_households01,
                       @underground_area01                 underground_area01,
                       @underground_floor_height01         underground_floor_height01,
                       @house_type_underground02           house_type_underground02,
                       @number_of_underground_households02 number_of_underground_households02,
                       @underground_area02                 underground_area02,
                       @underground_floor_height02         underground_floor_height02
            END
            """
#     with conn:
#         cursor.execute(sql1)
#         cursor.execute(sql2)
#         cursor.execute('EXECUTE getBuildingInfo ?', project_id)
#         row = cursor.fetchone()
#         row[5] = deduplication(row[5], 0)
#         row[9] = deduplication(row[9])
#         row[10] = deduplication(row[10])
#         row[11] = deduplication(row[11])
#         row[12] = deduplication(row[12], 0)
#         row[14] = deduplication(row[14])
#         row[15] = deduplication(row[15])
#         row[16] = deduplication(row[16], 0)
#         row[18] = deduplication(row[18])
#         row[19] = deduplication(row[19])
#     return row
#
#
# def deduplication(value, is_number=1):
#     val = value.split(',')[:-1]
#     val = list(set(val))
#     if len(val) == 1:
#         if is_number:
#             value = str(Decimal(val[0]).quantize(Decimal('0.00')))
#         else:
#             value = val[0]
#     else:
#         value = val
#     return value


data = get_certificate_info('90009210')
print(data)
